<!DOCTYPE html>

<html>
<head>
    <meta http-equiv="Content-Type" content="text/html; charset=UTF-8"/>
    <title>Database - Raxan User Guide</title>
    <link href="../raxan/ui/css/master.css" rel="stylesheet" type="text/css" />
    <!--[if lt IE 8]> <link rel="stylesheet" href="../raxan/ui/css/master.ie.css" type="text/css"><![endif]-->
    <link href="../raxan/ui/css/default/theme.css" rel="stylesheet" type="text/css" />
    <link href="style.css" rel="stylesheet" type="text/css" />
    <link href="highlight/styles/default.css" rel="stylesheet" type="text/css" />
    <script type="text/javascript" src="highlight/highlight.js"></script>
    <script type="text/javascript">
        <!--
        hljs.initHighlightingOnLoad('javascript','html','php','css');
        //-->
    </script>
    <!--[if lt IE 7]>
        <style type="text/css"> form input.textbox { height: 26px; }</style>
    <![endif]-->
</head>

<body>
    <div class="container">
        <div id="header" class="rax-header-pal rax-metalic">
            <h2 class="ltm bottom c14">Raxan User Guide</h2>
            <ul>
                <li><a href="../../index.html">Home</a></li>
                <li><a href="index.html">Overview</a></li>
                <li><a href="features.html">Features</a></li>
                <li><a href="table-of-contents.html" title="Table of Content">Contents</a></li>
                <li><a href="../examples">Examples</a></li>
            </ul>
            <ul class="search">
                <li>
                    <form class="tpm c9" name="form1" action="../tools/search.php" method="get">
                        <input class="c6 textbox round left" placeholder="Search" type="text" name="q" value="" title="Search User Guide"  />
                        <input class="c2 button round left ltm" type="submit" value="Go" />
                    </form>
                </li>
            </ul>

        </div>
        <hr class="space"/>
        <div class="master-content-wrapper">
            <div class="container prepend-top c48 master-content"><h2>Database</h2>

<p>Raxan uses PDO (PHP Data Objects) to establish connections to databases such as (MySQL, Oracle, Postregress, etc). The correct PDO driver must be
loaded before you can make a connection to the database.</p>

<h3>Connecting to a Database</h3>

<p>To connect to a database use the Raxan::connect() method by passing in the dsn, user id and password or a predefined database connection name:</p>

<pre><code class="php">&lt;?php

    require_once('raxan/pdi/autostart.php');

    class NewPage extends RaxanWebPage {

        protected function _config() {
            $this-&gt;masterTemplate = 'views/master.html';
        }

        protected function _init($e) {
            try {
                // connect to MySQL Server
                $dsn = 'mysql:host=localhost;dbname=employees';
                $this-&gt;db = $this-&gt;Raxan-&gt;connect($dsn,'user','password',true);

                // connect to SQL Server using driver pdo_sqlserv 2.0+
                $dsn = 'sqlserv: server=localhost; database=employees';
                $this-&gt;db = $this-&gt;Raxan-&gt;connect($dsn,'user','password',true);

                // or use the db.default config parameter name
                // $this-&gt;db = Raxan::connect('default');
            }
            catch (Exception $e) {
                $this-&gt;halt('Error while connecting to Database Server.');
            }
        }
    }

?&gt;
</code></pre>

<p>Note: Predefined database connection values can be stored within the configuration file. See <a href="custom-config.html">Loading A Custom Configuration File</a>.
For more information on Microsoft SQL Server driver for PHP visit <a href="http://blogs.msdn.com/b/sqlphp/">http://blogs.msdn.com/b/sqlphp/</a>.</p>

<p>The Raxan::connect() method will establish a connection with the database and returns an instance of the RaxanPDO class. You can use the connection object
in the same way you would use a normal PDO object.</p>

<h3>Retrieve records from the Database</h3>

<p>The example below shows how to use the <strong>RaxanPDO::table()</strong> helper method to retrieve data from a table:</p>

<pre><code class="php">&lt;?php

    protected function _load($e) {

        // retrieve all records from employees table
        $rows = $this-&gt;db-&gt;table('employees');

        // retrieve columns first_name and last_name from employees table
        $rows = $this-&gt;db-&gt;table('employees first_name, last_name');

        // retrieve records from the employees table where last_name= john or williams
        $rows = $this-&gt;db-&gt;table('employees','last_name = ? or last_name = ?','james','williams');

        // use names parameters to filter records
        $params = array(':first' =&gt; 'John', ':last'=&gt; 'Brown');
        $rows = $this-&gt;db-&gt;table('employees','first_name = :first or last_name = :last, $params);

        // once you have retrieved the records from the database you bind it to any element
        $this-&gt;list1-&gt;bind($rows);

    }

?&gt;
</code></pre>

<p>In addition to using the table() method you can also use the execQuery() helper method to execute parameterized queries as shown below:</p>

<pre><code class="php">&lt;?php

    protected function _load($e) {

        $sql = 'select o.*, c.fname, c.lname from order o '.
               'inner join customer c on c.id = o.customer_id '.
               'where o.date between ? and ?';
        $rows = $this-&gt;db-&gt;execQuery($sql,'2010-03-01', '2010-03-31');

    }

?&gt;
</code></pre>

<h3>Adding Records to a Table</h3>

<p>To add a record to a table, use the RaxanPDO::tableInsert() helper method as shown below:</p>

<pre><code class="php">&lt;?php

    protected function _load($e) {

        $data = array(
            'first_name' =&gt; 'Mary',
            'last_name' =&gt; 'Jane'
            'email' =&gt; 'maryj@somewhere.domain.com'
        );

        // add record to the employees table
        $rows = $this-&gt;db-&gt;tableInsert('employees',$data);

        // get new employee id
        $id = $this-&gt;db-&gt;lastInsertId();

    }

?&gt;
</code></pre>

<h3>Updating Records within a Table</h3>

<p>To update a record within a table, use the RaxanPDO::tableUpdate() helper method as shown below:</p>

<pre><code class="php">&lt;?php

    protected function _load($e) {

        $data = array(
            'first_name' =&gt; 'Mary',
            'last_name' =&gt; 'Jane'
            'email' =&gt; 'maryj@somewhere.domain.com'
        );

        // update a record inside the employees table
        $rows = $this-&gt;db-&gt;tableUpdate('employees',$data,'id=?',23);

    }

?&gt;
</code></pre>

<h3>Deleting Records from a Table</h3>

<p>To delete a record from a table, use the RaxanPDO::tableDelete() helper method as shown below:</p>

<pre><code class="php">&lt;?php

    protected function _load($e) {

        // delete record from the employees table
        $rows = $this-&gt;db-&gt;tableDelete('employees','id=?',23);

    }

?&gt;
</code></pre>

<h3>Calling a Store Procedure</h3>

<p>To call a store procedure you can use the RaxanPDO::execProc</p>

<pre><code class="php">&lt;?php

    protected function _load() {
        $param1 = 'First message';
        $param2 = 'Second message';
        // setup an input/output parameter -  array(value, datatype, length)
        $param3 = $out = array(null,PDO::PARAM_STR|PDO::PARAM_INPUT_OUTPUT,2048);
        $rt = $this-&gt;db-&gt;execProc('sp_name',$param1, $param2, $param3); // $param1...$paramN

        // display message column value from first row
        $this-&gt;div3-&gt;text('Rowset: '.$rt-&gt;rowset[0]['message']);

        // get next rowset if multiple rowsets where returned by the SP
        $next = $rt-&gt;nextRowset;

        // display returned value from SP
        $this-&gt;div1-&gt;text('The returned value is '.$rt-&gt;returnValue);

        // display output value for input/output parameter
        $this-&gt;div2-&gt;text('Output value is '.$rt-&gt;parameters[2]);  // zero base array

    }

?&gt;
</code></pre>

<p><strong>Microsoft SQL Server Tip</strong> - When working with Stored Procedures, SQL Server requires that you either consume or skip the rowset in order
to get the output parameters.</p>

<p>See also <a href="raxanpdo.html">RaxanPDO</a> class reference for more information.</p>

<hr class="clear" />

<p align="right">Up Next: <a href="global-functions.html" title="Global Functions">Global Functions</a> </p>
</div>
            
            <div id="footer" class="container c48 rax-active-pal round rax-glossy">
                <ul class="clearfix">
                    <li><a href="index.html">Overview</a></li>
                    <li><a href="features.html">Features</a></li>
                    <li><a href="new-features.html">What's new</a></li>
                    <li><a href="table-of-contents.html" title="Table of Content">Contents</a></li>
                    <li><a href="../examples">Examples</a></li>
                </ul>
            </div>
        </div>
    </div>
</body>

</html>

